-- readers
insert into reader(reader_id,fname,lname,zip_code,city,address,email) 
 values(reader_seq.nextval,'Luis','Figo','38xcf23','Lisbon','Portugal','lfigo@yahoo.com');
insert into reader(reader_id,fname,lname,zip_code,city,address,email) 
 values(reader_seq.nextval,'Mscislaw','Msciwor','28-530','Kazimierza Wielka','Poland','msci@wp.pl');
insert into reader(reader_id,fname,lname,zip_code,city,address,email) 
 values(reader_seq.nextval,'Aleksiej','Sanakojew','38x543','Moscow','Russia','sanak@yahoo.com');
insert into reader(reader_id,fname,lname,zip_code,city,address,email)
 values(reader_seq.nextval,'Roman','Kucharczyk','33-456','Cracow','Poland','roman.kucharczyk@gmail.com');

-- categories
insert into category(category_id,name,parent_category_id) 
 values(category_seq.nextval,'chess',null);
insert into category(category_id,name,parent_category_id) 
 values(category_seq.nextval,'cuisine',null);
insert into category(category_id,name,parent_category_id) 
 values(category_seq.nextval,'football',null);

insert into category(category_id,name,parent_category_id) 
 values(category_seq.nextval,'chess strategy',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'chess tactics',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'chess endings',null);

insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'asian cuisine',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'greek cuisine',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'chinese cuisine',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'polish cuisine',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'borderland cuisine',null);

insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'football strategy',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'football tactics',null);
insert into category(category_id,name,parent_category_id)
 values(category_seq.nextval,'football training',null);


-- updating categories to set subcategories
update category set parent_category_id=(select sc.category_id from category sc where sc.name='chess')
 where name like 'chess %';

update category set parent_category_id=(select sc.category_id from category sc where sc.name='cuisine')
 where name like '% cuisine';

update category set parent_category_id=(select sc.category_id from category sc where sc.name='polish cuisine')
 where name='borderland cuisine';

update category set parent_category_id=(select sc.category_id from category sc where sc.name='football')
 where name like 'football %';

-- books
insert into book(book_id,title,pages_num,isbn,publishing_date,category_id)
 values(book_seq.nextval,'The adventure in the kitchen',670,null,null,(select category_id from category where name='polish cuisine'));

insert into book(book_id,title,pages_num,isbn,publishing_date,category_id)
 values(book_seq.nextval,'The chinese food',670,'978-83-246-3412-4',null,(select category_id from category where name='chinese cuisine'));

insert into book(book_id,title,pages_num,isbn,publishing_date,category_id)
 values(book_seq.nextval,'Excelling at positional chess',140,'968-83-246-3412-4',null,(select category_id from category where name='chess strategy'));

insert into book(book_id,title,pages_num,isbn,publishing_date,category_id)
 values(book_seq.nextval,'Positional play',140,'968-13-246-3412-4',null,(select category_id from category where name='chess strategy'));


-- authors
insert into author(author_id,fname,lname)
 values(author_seq.nextval,'Jacob','Aagard');

insert into author(author_id,fname,lname)
 values(author_seq.nextval,'Mark','Dworecki');

insert into author(author_id,fname,lname)
 values(author_seq.nextval,'Artur','Jusupow');

insert into author(author_id,fname,lname)
 values(author_seq.nextval,'Marek','Wtorek');

insert into author(author_id,fname,lname)
 values(author_seq.nextval,'Xie','Yun');

--author_book
insert into author_book(author_id,book_id)
 values((select author_id from author where lname='Wtorek'),(select book_id from book where title='The adventure in the kitchen'));

insert into author_book(author_id,book_id)
 values((select author_id from author where lname='Yun'),(select book_id from book where title='The chinese food'));

insert into author_book(author_id,book_id)
 values((select author_id from author where lname='Aagard'),(select book_id from book where title='Excelling at positional chess'));

insert into author_book(author_id,book_id)
 values((select author_id from author where lname='Jusupow'),(select book_id from book where title='Positional play'));

insert into author_book(author_id,book_id)
 values((select author_id from author where lname='Dworecki'),(select book_id from book where title='Positional play'));

-- hire
insert into hire(hire_id,hire_date,return_date,reader_id,book_id)
 values(hire_seq.nextval,current_timestamp, null,
       (select reader_id from reader where lname='Sanakojew'),
       (select book_id from book where title='Positional play'));

insert into hire(hire_id,hire_date,return_date,reader_id,book_id)
 values(hire_seq.nextval,current_timestamp, null,
       (select reader_id from reader where lname='Msciwor'),
       (select book_id from book where title='The adventure in the kitchen'));

commit;
